﻿using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

using LTJ.Data;
using LTJ.Common;
using LTJ.Config;
using LTJ.Entity;

namespace LTJ.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        #region 企业信息操作
        /// <summary>
        /// 创建企业信息
        /// </summary>
        public int CreateCompany(CompanyInfo cinfo)
        {
            DbParameter[] parms = {
			                        DbHelper.MakeInParam("@cname", (DbType)SqlDbType.NVarChar,50,cinfo.cname),
					                DbHelper.MakeInParam("@ecname", (DbType)SqlDbType.VarChar,100,cinfo.ecname),
					                DbHelper.MakeInParam("@logo", (DbType)SqlDbType.VarChar,200,cinfo.logo),
					                DbHelper.MakeInParam("@welcome", (DbType)SqlDbType.VarChar,200,cinfo.welcome),
					                DbHelper.MakeInParam("@relater", (DbType)SqlDbType.NVarChar,20,cinfo.relater),
					                DbHelper.MakeInParam("@relateform", (DbType)SqlDbType.VarChar,0,cinfo.relateform),
					                DbHelper.MakeInParam("@bio", (DbType)SqlDbType.VarChar,0,cinfo.bio),
					                DbHelper.MakeInParam("@status", (DbType)SqlDbType.Int,4,cinfo.status),
                                    DbHelper.MakeInParam("phone", (DbType)SqlDbType.VarChar,20, cinfo.Phone),
			                        DbHelper.MakeInParam("scores", (DbType)SqlDbType.Int,4, cinfo.Scores),
			                        DbHelper.MakeInParam("main", (DbType)SqlDbType.VarChar,200, cinfo.Mains),
			                        DbHelper.MakeInParam("creater", (DbType)SqlDbType.VarChar,20, cinfo.Creater),
                                    DbHelper.MakeInParam("email", (DbType)SqlDbType.VarChar,200, cinfo.Email),
                                    DbHelper.MakeInParam("weburl", (DbType)SqlDbType.VarChar,200, cinfo.WebUrl),
                                    DbHelper.MakeInParam("msn", (DbType)SqlDbType.VarChar,200, cinfo.MSN),
                                    DbHelper.MakeInParam("mcount",(DbType)SqlDbType.Int,4,cinfo.mcount),
                                    DbHelper.MakeInParam("ucount",(DbType)SqlDbType.Int,4,cinfo.ucount)
                                  };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}company_ADD", BaseConfigs.GetTablePrefix), parms), -1);
        }
        
        /// <summary>
        /// 根据查询条件获取企业数量
        /// </summary>
        public int GetCompanyCountByCondition(string querystring)
        {
            DbParameter[] parms = { DbHelper.MakeInParam("@conditions", (DbType)SqlDbType.VarChar, 2000, querystring) };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}getcompanycountbycondition", BaseConfigs.GetTablePrefix), parms));
        }
        /// <summary>
        /// 企业数据分页操作
        /// </summary>
        public DataTable GetCompanyPageList(int pageindex, int pagesize, string conditions,string orderstr)
        {
            DbParameter[] parms = {
                          DbHelper.MakeInParam("@pageindex",(DbType)SqlDbType.Int,4,pageindex),
                          DbHelper.MakeInParam("@pagesize",(DbType)SqlDbType.Int,4,pagesize),
                          DbHelper.MakeInParam("@conditions",(DbType)SqlDbType.NVarChar,2000,conditions),
                          DbHelper.MakeInParam("@orderstr",(DbType)SqlDbType.NVarChar,2000,orderstr)

            };
            return DbHelper.ExecuteDataset(CommandType.StoredProcedure, string.Format("{0}getcompanylistbypage", BaseConfigs.GetTablePrefix), parms).Tables[0];
        }
        /// <summary>
        /// 获取企业信息
        /// </summary>
        public IDataReader GetCompanyByID(int id)
        {
            DbParameter[] parms = {
									   DbHelper.MakeInParam("@id",(DbType)SqlDbType.Int,4,id),
			                      };
            return DbHelper.ExecuteReader(CommandType.StoredProcedure, string.Format("{0}company_GetModel", BaseConfigs.GetTablePrefix), parms);
        }
        /// <summary>
        /// 更新企业信息
        /// </summary>
        public void UpdateCompany(CompanyInfo cinfo)
        {
            DbParameter[] parms = {
                                    DbHelper.MakeInParam("id",(DbType)SqlDbType.Int,4,cinfo.id),
			                        DbHelper.MakeInParam("@cname", (DbType)SqlDbType.NVarChar,50,cinfo.cname),
					                DbHelper.MakeInParam("@ecname", (DbType)SqlDbType.VarChar,100,cinfo.ecname),
					                DbHelper.MakeInParam("@logo", (DbType)SqlDbType.VarChar,200,cinfo.logo),
					                DbHelper.MakeInParam("@welcome", (DbType)SqlDbType.VarChar,200,cinfo.welcome),
					                DbHelper.MakeInParam("@relater", (DbType)SqlDbType.NVarChar,20,cinfo.relater),
					                DbHelper.MakeInParam("@relateform", (DbType)SqlDbType.VarChar,0,cinfo.relateform),
					                DbHelper.MakeInParam("@bio", (DbType)SqlDbType.VarChar,0,cinfo.bio),
					                DbHelper.MakeInParam("@status", (DbType)SqlDbType.Int,4,cinfo.status),
                                    DbHelper.MakeInParam("@phone", (DbType)SqlDbType.VarChar,20, cinfo.Phone),
			                        DbHelper.MakeInParam("@scores", (DbType)SqlDbType.Int,4, cinfo.Scores),
			                        DbHelper.MakeInParam("@main", (DbType)SqlDbType.VarChar,200, cinfo.Mains),
                                    DbHelper.MakeInParam("email", (DbType)SqlDbType.VarChar,200, cinfo.Email),
                                    DbHelper.MakeInParam("weburl", (DbType)SqlDbType.VarChar,200, cinfo.WebUrl),
                                    DbHelper.MakeInParam("msn", (DbType)SqlDbType.VarChar,200, cinfo.MSN),
                                    DbHelper.MakeInParam("mcount",(DbType)SqlDbType.Int,4,cinfo.mcount),
                                    DbHelper.MakeInParam("ucount",(DbType)SqlDbType.Int,4,cinfo.ucount)
                                  };
            DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, string.Format("{0}company_Update", BaseConfigs.GetTablePrefix), parms);
        }
        /// <summary>
        /// 企业批量删除
        /// </summary>
        public int DelCompany(string idlist)
        {
            idlist = LTJ.Common.Utils.returnIdList(idlist);
            string commandText = string.Format("DELETE [{0}company] WHERE [id] IN (" + idlist + ")", BaseConfigs.GetTablePrefix, idlist);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText);
        }
        /// <summary>
        /// 判断企业是否存在
        /// </summary>
        public int IsExistCompany(string companyname)
        {
            DbParameter[] parms = {
			                        DbHelper.MakeInParam("@cname", (DbType)SqlDbType.NVarChar,50,companyname)                                  
                                  };
            string commandText = string.Format("SELECT COUNT([id]) FROM [{0}company] WHERE [cname]=@cname", BaseConfigs.GetTablePrefix);
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.Text, commandText, parms), 0);
        }

        /// <summary>
        /// 更改企业状态
        /// </summary>
        public bool UpdateCorpStatus(string ids, int status)
        {
            string commandText = string.Format("UPDATE [{0}company] SET [status] = {1} WHERE [id] IN ({2})", BaseConfigs.GetTablePrefix, status, ids);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText) > 0;
        }
        /// <summary>
        /// 设置企业积分
        /// </summary>
        public int SetCorpScore(int cid, int scores)
        {
            string commandText = string.Format("UPDATE [{0}company] SET [scores] = {1} WHERE [id] = {2}", BaseConfigs.GetTablePrefix, scores, cid);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText);
        }
        #endregion    

        #region 基础类型操作
        /// <summary>
        /// 创建基础类型
        /// </summary>
        /// <param name="code">类型代码</param>
        /// <param name="tname">类型名称</param>
        /// <param name="ename">类型英文</param>
        /// <param name="extends">类型扩展</param>
        /// <returns></returns>
        public string CreateBaseType(string code, string tname, string ename, string extends)
        {
            DbParameter[] parms = {
			                        DbHelper.MakeInParam("@code", (DbType)SqlDbType.Char,40,code),
					                DbHelper.MakeInParam("@tname", (DbType)SqlDbType.NVarChar,20,tname),
					                DbHelper.MakeInParam("@etname", (DbType)SqlDbType.VarChar,50,ename),
					                DbHelper.MakeInParam("@expand", (DbType)SqlDbType.VarChar,500,extends)
                                  };
            return DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}basetype_ADD", BaseConfigs.GetTablePrefix), parms).ToString();
        }
        /// <summary>
        /// 根据Code是否存在
        /// </summary>
        /// <param name="code"></param>
        public IDataReader GetBaseTypeCode(string code)
        {
            DbParameter[] parms = {
									   DbHelper.MakeInParam("@code",(DbType)SqlDbType.Char,40,code),
			                      };
            string commandText = string.Format("SELECT TOP 1 {0} FROM [{1}basetype] WHERE [code]=@code",
                                                DbFields.BASETYPE,
                                                BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteReader(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 修改基础类型信息
        /// </summary>
        public void UpdateBaseType(string code, string tname, string ename, string extends)
        {
            DbParameter[] parms = {
			                        DbHelper.MakeInParam("@code", (DbType)SqlDbType.Char,40,code),
					                DbHelper.MakeInParam("@tname", (DbType)SqlDbType.NVarChar,20,tname),
					                DbHelper.MakeInParam("@etname", (DbType)SqlDbType.VarChar,50,ename),
					                DbHelper.MakeInParam("@expand", (DbType)SqlDbType.VarChar,500,extends)
                                  };
            DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, string.Format("{0}basetype_Update", BaseConfigs.GetTablePrefix), parms);
        }
        /// <summary>
        /// 基础类型分页
        /// </summary>
        public DataTable GetBaseTypesByPage(int pageindex, int pagesize, string condition, string orderstr)
        {
            DbParameter[] parms = {
                          DbHelper.MakeInParam("@pageindex",(DbType)SqlDbType.Int,4,pageindex),
                          DbHelper.MakeInParam("@pagesize",(DbType)SqlDbType.Int,4,pagesize),
                          DbHelper.MakeInParam("@conditions",(DbType)SqlDbType.NVarChar,2000,condition),
                          DbHelper.MakeInParam("@orderstr",(DbType)SqlDbType.NVarChar,2000,orderstr)
            };
            return DbHelper.ExecuteDataset(CommandType.StoredProcedure, string.Format("{0}getbasetypebypage", BaseConfigs.GetTablePrefix), parms).Tables[0];
        }
        /// <summary>
        /// 基础类型数量
        /// </summary>
        public int GetBaseTypeCount(string conditions)
        {
            DbParameter[] parms = { DbHelper.MakeInParam("@conditions", (DbType)SqlDbType.VarChar, 2000, conditions) };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}getbasetypecountbycondition", BaseConfigs.GetTablePrefix), parms));
        }
        /// <summary>
        /// 基础类型删除
        /// </summary>
        public int DelBaseType(string bcode)
        {
            string commandText = string.Format("DELETE [{0}basetype] WHERE [code] ='{1}'", BaseConfigs.GetTablePrefix, bcode);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText);
        }

        /// <summary>
        /// 根据基础类型code查询是否有基础数据
        /// </summary>
        /// <param name="bcode"></param>
        public DataTable GetBaseDataByTypecode(string bcode)
        {
            DbParameter[] parms = {
									   DbHelper.MakeInParam("@bcode",(DbType)SqlDbType.Char,40,bcode),
			                      };
            string commandText = string.Format("SELECT  {0} FROM [{1}basedata] WHERE [code]=@bcode",
                                                DbFields.BASEDATA,
                                                BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, parms).Tables[0];
        }
        #endregion

        #region 基础数据操作
        /// <summary>
        /// 创建基础数据
        /// </summary>
        public int CreateBaseData(BaseDataInfo binfo)
        {
            DbParameter[] parms = {
					                DbHelper.MakeInParam("@code", (DbType)SqlDbType.Char,40,binfo.code),
					                DbHelper.MakeInParam("@tcode", (DbType)SqlDbType.Char,40,binfo.tcode),
					                DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar,20,binfo.name),
					                DbHelper.MakeInParam("@ename", (DbType)SqlDbType.VarChar,50,binfo.ename),
					                DbHelper.MakeInParam("@expand1", (DbType)SqlDbType.VarChar,0,binfo.expand1),
					                DbHelper.MakeInParam("@expand2", (DbType)SqlDbType.VarChar,0,binfo.expand2),
					                DbHelper.MakeInParam("@expand3", (DbType)SqlDbType.VarChar,0,binfo.expand3)
                                  };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}basedata_ADD", BaseConfigs.GetTablePrefix), parms), -1);
        }
        /// <summary>
        /// 查询当前数据是否唯一
        /// </summary>
        /// <param name="code">数据代码</param>
        /// <param name="tcode">数据类型</param>
        public bool ExistBaseData(string code, string tcode)
        {
            DbParameter[] parms = {
									   DbHelper.MakeInParam("@code",(DbType)SqlDbType.Char,40,code),
                                       DbHelper.MakeInParam("@tcode",(DbType)SqlDbType.Char,40,tcode)
			                      };
            string commandText = string.Format("SELECT COUNT(id) FROM [{0}basedata] WHERE [code] = @code AND [tcode] = @tcode", BaseConfigs.GetTablePrefix);
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.Text, commandText, parms)) > 0 ? true : false;
        }
        /// <summary>
        /// 修改基础数据
        /// </summary>
        /// <param name="binfo"></param>
        public void UpdateBaseData(BaseDataInfo binfo)
        {
            DbParameter[] parms = {
                                    DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int,4,binfo.id),
					                DbHelper.MakeInParam("@code", (DbType)SqlDbType.Char,40,binfo.code),
					                DbHelper.MakeInParam("@tcode", (DbType)SqlDbType.Char,40,binfo.tcode),
					                DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar,20,binfo.name),
					                DbHelper.MakeInParam("@ename", (DbType)SqlDbType.VarChar,50,binfo.ename),
					                DbHelper.MakeInParam("@expand1", (DbType)SqlDbType.VarChar,0,binfo.expand1),
					                DbHelper.MakeInParam("@expand2", (DbType)SqlDbType.VarChar,0,binfo.expand2),
					                DbHelper.MakeInParam("@expand3", (DbType)SqlDbType.VarChar,0,binfo.expand3)
                                  };
            DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, string.Format("{0}basedata_Update", BaseConfigs.GetTablePrefix), parms);
        }
        /// <summary>
        /// 基础数据分页
        /// </summary>
        public DataTable GetBaseDataByPage(int pageindex, int pagesize, string condition, string orderstr)
        {
            DbParameter[] parms = {
                          DbHelper.MakeInParam("@pageindex",(DbType)SqlDbType.Int,4,pageindex),
                          DbHelper.MakeInParam("@pagesize",(DbType)SqlDbType.Int,4,pagesize),
                          DbHelper.MakeInParam("@conditions",(DbType)SqlDbType.NVarChar,2000,condition),
                          DbHelper.MakeInParam("@orderstr",(DbType)SqlDbType.NVarChar,2000,orderstr)
            };
            return DbHelper.ExecuteDataset(CommandType.StoredProcedure, string.Format("{0}getbasedatabypage", BaseConfigs.GetTablePrefix), parms).Tables[0];
        }
        public DataTable GetBaseDataListByStr(string strWhere)
        {

            string sql = string.Format("SELECT  {0} FROM [{1}basedata] ", DbFields.BASEDATA, BaseConfigs.GetTablePrefix);
            if (strWhere != "")
            {
                sql += " where " + strWhere;
            }
            return DbHelper.ExecuteDataset(CommandType.Text, sql).Tables[0];
        }
        public DataTable GetDestportsMsg(string strWhere)
        {
            string sql = "select a.*,b.name as sshx,c.name as ssgj from dbo.ls_basedata as a  left join dbo.ls_basedata as b on a.expand2=b.tcode and b.code='searoute' left join dbo.ls_basedata as c on a.expand3=c.tcode and c.code='country'";
            if (strWhere != "")
            {
                sql += " where " + strWhere;
            }
            return DbHelper.ExecuteDataset(CommandType.Text, sql).Tables[0];
        }
        public DataTable GetCompBaseDataListByStr(int pageindex, int pagesize, string condition, string orderstr)
        {
            DbParameter[] parms = {
                          DbHelper.MakeInParam("@pageindex",(DbType)SqlDbType.Int,4,pageindex),
                          DbHelper.MakeInParam("@pagesize",(DbType)SqlDbType.Int,4,pagesize),
                          DbHelper.MakeInParam("@conditions",(DbType)SqlDbType.NVarChar,2000,condition),
                          DbHelper.MakeInParam("@orderstr",(DbType)SqlDbType.NVarChar,2000,orderstr)
            };
            return DbHelper.ExecuteDataset(CommandType.StoredProcedure, string.Format("{0}getbasedatabypage_comp", BaseConfigs.GetTablePrefix), parms).Tables[0];
        }
        /// <summary>
        /// 基础数据数量
        /// </summary>
        public int GetBaseDataCount(string conditions)
        {
            DbParameter[] parms = { DbHelper.MakeInParam("@conditions", (DbType)SqlDbType.VarChar, 2000, conditions) };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}getbasedatacountbycondition", BaseConfigs.GetTablePrefix), parms));
        }
        public int GetCompBaseDataCount(string conditions)
        {
            DbParameter[] parms = { DbHelper.MakeInParam("@conditions", (DbType)SqlDbType.VarChar, 2000, conditions) };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}getbasedatacountbycondition_comp", BaseConfigs.GetTablePrefix), parms));
        }
        
        /// <summary>
        /// 根据ID获取基本数据信息实体
        /// </summary>
        public IDataReader GetBaseDataByID(int bid)
        {
            DbParameter[] parms = {
									   DbHelper.MakeInParam("@id",(DbType)SqlDbType.Int,4,bid),
			                      };
            return DbHelper.ExecuteReader(CommandType.StoredProcedure, string.Format("{0}basedata_GetModel", BaseConfigs.GetTablePrefix), parms);
        }
        /// <summary>
        /// 根据ID获取基本数据信息实体
        /// </summary>
        public IDataReader GetBaseDataByStr(string strWhere)
        {
            string sql = string.Format("SELECT TOP 1 {0} FROM [{1}basedata] ", DbFields.BASEDATA, BaseConfigs.GetTablePrefix);
            if (strWhere != "")
            {
                sql += " where " + strWhere;
            }
            return DbHelper.ExecuteReader(CommandType.Text, sql);
        }
        /// <summary>
        /// 基础数据批量删除
        /// </summary>
        public int DelBaseData(string idlist)
        {
            idlist = LTJ.Common.Utils.returnIdList(idlist);
            string commandText = string.Format("DELETE [{0}basedata] WHERE [id] IN (" + idlist + ")", BaseConfigs.GetTablePrefix, idlist);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText);
        }

        /// <summary>
        /// 返回基础数据查询基本条件
        /// </summary>
        /// <param name="scode">基础数据类型code(必填）</param>
        /// <param name="hxstr">航线集合</param>
        /// <param name="searchcname">检索中文名</param>
        /// <param name="searchename">检索英文名</param>
        /// <param name="datatype">数据查询类型</param>
        public string GetBaseDataCondation(string scode, string hxstr, string searchcname, string searchename, int datatype)
        {
            string querystr = " code = '" + scode + "' ";

            if (hxstr != "" && datatype == 1) querystr += " AND expand2 IN (" + hxstr + ") ";
            if (hxstr != "" && datatype == 2) querystr += " AND tcode IN (" + hxstr + ") ";
            if (hxstr != "" && datatype == Convert.ToInt16(DataMode.RouteArray)) querystr += " AND tcode NOT IN (" + hxstr + ") ";
            if (datatype == 3) querystr += " AND expand1 = 1 ";
            if (searchcname != "") querystr += " AND name LIKE '%" + searchcname + "%'";
            if (searchename != "") querystr += " AND ename LIKE '%" + searchename + "%'";

            return querystr;
        }
        #endregion

        #region 航线授权操作
        /// <summary>
        /// 创建航线授权信息
        /// </summary>
        public int CreateRoute(RouteInfo rinfo)
        {
            DbParameter[] parms = {
                                    DbHelper.MakeInParam("@cid", (DbType)SqlDbType.Int,4,rinfo.cid),
                                    DbHelper.MakeInParam("@tcode", (DbType)SqlDbType.Char,40,rinfo.tcode),
                                    DbHelper.MakeInParam("@tname", (DbType)SqlDbType.NVarChar,20,rinfo.tname),
                                    DbHelper.MakeInParam("@bcode", (DbType)SqlDbType.Char,40,rinfo.bcode),
                                    DbHelper.MakeInParam("@bname", (DbType)SqlDbType.NVarChar,20,rinfo.bname),
                                    DbHelper.MakeInParam("@creater", (DbType)SqlDbType.VarChar,20,rinfo.creater)
                                  };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}route_ADD", BaseConfigs.GetTablePrefix), parms), -1);
        }
        public int AddRoute(DataTable dt)
        {
            int rowsAffected = 0;
            SqlParameter[] parameters = {
                    new SqlParameter("@cid", SqlDbType.Int,4),
					new SqlParameter("@tcode", SqlDbType.Char,40),
					new SqlParameter("@tname", SqlDbType.NVarChar,20),
					new SqlParameter("@bcode", SqlDbType.Char,40),
                    new SqlParameter("@bname", SqlDbType.NVarChar,20),
                    new SqlParameter("@creater", SqlDbType.VarChar,20)
                };
            foreach (DataRow dr in dt.Rows)
            {
                parameters[0].Value =Utils.StrToInt(dr["cid"].ToString(),0);
                parameters[1].Value = dr["tcode"].ToString();
                parameters[2].Value = dr["tname"].ToString();
                parameters[3].Value = dr["bcode"].ToString();
                parameters[4].Value = dr["bname"].ToString();
                parameters[5].Value = dr["creater"].ToString();
                rowsAffected = 0;
                try
                {
                    rowsAffected = TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}route_ADD", BaseConfigs.GetTablePrefix), parameters), -1);
                }
                catch (Exception eee)
                {
                }
            }

            return rowsAffected;
            
        }
        /// <summary>
        /// 判断航线重复授权
        /// </summary>
        /// <param name="cid">公司ID</param>
        /// <param name="tcode">航线Code</param>
        /// <param name="bcode">船公司code</param>
        public bool ExistReRoute(int cid, string tcode, string bcode)
        {
            DbParameter[] parms = {
                                    DbHelper.MakeInParam("@cid", (DbType)SqlDbType.Int,4,cid),
                                    DbHelper.MakeInParam("@tcode", (DbType)SqlDbType.Char,40,tcode),
                                    DbHelper.MakeInParam("@bcode", (DbType)SqlDbType.Char,40,bcode)
                                  };
            string commandText = string.Format("SELECT COUNT(id) FROM [{0}route] WHERE [cid] = @cid AND [tcode] = @tcode AND [bcode] = @bcode", BaseConfigs.GetTablePrefix);
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.Text, commandText, parms)) > 0 ? true : false;
        }
        /// <summary>
        /// 根据ID获取授权信息
        /// </summary>
        public IDataReader GetRountByID(int id)
        {
            DbParameter[] parms = {
									   DbHelper.MakeInParam("@id",(DbType)SqlDbType.Int,4,id),
			                      };
            string commandText = string.Format("SELECT TOP 1 {0} FROM [{1}route] WHERE [id]=@id",
                                                DbFields.ROUTE,
                                                BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteReader(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 根据条件获取授权信息
        /// </summary>
        public IDataReader GetRountByStr(string strWhere)
        {
            string sql = string.Format("SELECT TOP 1 {0} FROM [{1}route] ", DbFields.ROUTE, BaseConfigs.GetTablePrefix);
            if (strWhere != "")
            {
                sql += " where " + strWhere;
            }
            return DbHelper.ExecuteReader(CommandType.Text, sql);
        }
        
        /// <summary>
        /// 根据条件获取授权信息
        /// </summary>
        public DataTable  GetRountTabByStr(string strWhere)
        {
            string sql = string.Format("SELECT {0} FROM [{1}route] ", DbFields.ROUTE, BaseConfigs.GetTablePrefix);
            if (strWhere != "")
            {
                sql += " where " + strWhere;
            }
            return DbHelper.ExecuteDataset(CommandType.Text, sql).Tables[0];
        }

        /// <summary>
        /// 根据条件获取授权信息
        /// </summary>
        public IDataReader GetRountList(string strWhere)
        {
            string sql = string.Format("SELECT {0} FROM [{1}route] ", DbFields.ROUTE, BaseConfigs.GetTablePrefix);
            if (strWhere != "")
            {
                sql += " where " + strWhere;
            }
            return DbHelper.ExecuteReader(CommandType.Text, sql);
        }
        
        /// <summary>
        /// 获取航线授权信息分页
        /// </summary>
        public DataTable GetRoutesByPage(int pageindex, int pagesize, string condition, string ordercolumn, string ordertype)
        {
            DbParameter[] parms = {
                          DbHelper.MakeInParam("@pageindex",(DbType)SqlDbType.Int,4,pageindex),
                          DbHelper.MakeInParam("@pagesize",(DbType)SqlDbType.Int,4,pagesize),
                          DbHelper.MakeInParam("@conditions",(DbType)SqlDbType.NVarChar,2000,condition),
                          DbHelper.MakeInParam("@ordercolumn",(DbType)SqlDbType.VarChar,20,ordercolumn),
                          DbHelper.MakeInParam("@ordertype",(DbType)SqlDbType.VarChar,5,ordertype)
            };
            return DbHelper.ExecuteDataset(CommandType.StoredProcedure, string.Format("{0}getroutesbypage", BaseConfigs.GetTablePrefix), parms).Tables[0];
        }
        /// <summary>
        /// 根据条件获取航线授权信息集合数量
        /// </summary>
        public int GetRoutesCount(string conditions)
        {
            DbParameter[] parms = { DbHelper.MakeInParam("@conditions", (DbType)SqlDbType.VarChar, 2000, conditions) };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}getroutescountbycondition", BaseConfigs.GetTablePrefix), parms));
        }
        /// <summary>
        /// 修改航线授权信息
        /// </summary>
        public int UpdateRoute(RouteInfo rinfo)
        {
            DbParameter[] parms = {
                                    DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int,4,rinfo.id),
                                    DbHelper.MakeInParam("@cid", (DbType)SqlDbType.Int,4,rinfo.cid),
                                    DbHelper.MakeInParam("@tcode", (DbType)SqlDbType.Char,40,rinfo.tcode),
                                    DbHelper.MakeInParam("@tname", (DbType)SqlDbType.NVarChar,20,rinfo.tname),
                                    DbHelper.MakeInParam("@bcode", (DbType)SqlDbType.Char,40,rinfo.bcode),
                                    DbHelper.MakeInParam("@bname", (DbType)SqlDbType.NVarChar,20,rinfo.bname),
                                    DbHelper.MakeInParam("@creater", (DbType)SqlDbType.VarChar,20,rinfo.creater)
                                  };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}route_Update", BaseConfigs.GetTablePrefix), parms), -1);
        }
        /// <summary>
        /// 航线授权批量删除
        /// </summary>
        public int DelRoutes(string idlist)
        {
            idlist = LTJ.Common.Utils.returnIdList(idlist);
            string commandText = string.Format("DELETE [{0}route] WHERE [id] IN (" + idlist + ")", BaseConfigs.GetTablePrefix, idlist);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText);
        }
        #endregion

        #region 航线基本信息操作
        /// <summary>
        /// 创建航线信息
        /// </summary>
        public int CreateRouteBase(RouteBaseInfo rinfo)
        {
            DbParameter[] parms = {
					                DbHelper.MakeInParam("@loadport", (DbType)SqlDbType.Char,40,rinfo.loadport),
					                DbHelper.MakeInParam("@destport", (DbType)SqlDbType.Char,40,rinfo.destport),
					                DbHelper.MakeInParam("@tranport", (DbType)SqlDbType.Char,40,rinfo.tranport),
					                DbHelper.MakeInParam("@bcode", (DbType)SqlDbType.Char,40,rinfo.bcode),
					                DbHelper.MakeInParam("@pier", (DbType)SqlDbType.Char,40,rinfo.pier),
					                DbHelper.MakeInParam("@schbegin", (DbType)SqlDbType.VarChar,20,rinfo.schbegin),
					                DbHelper.MakeInParam("@schend", (DbType)SqlDbType.VarChar,20,rinfo.schend),
					                DbHelper.MakeInParam("@range", (DbType)SqlDbType.VarChar,10,rinfo.range),
					                DbHelper.MakeInParam("@validbegin", (DbType)SqlDbType.SmallDateTime,8,rinfo.validbegin),
					                DbHelper.MakeInParam("@validend", (DbType)SqlDbType.SmallDateTime,8,rinfo.validend),
					                DbHelper.MakeInParam("@bio", (DbType)SqlDbType.VarChar,0,rinfo.bio),
					                DbHelper.MakeInParam("@cid", (DbType)SqlDbType.Int,4,rinfo.cid),
					                DbHelper.MakeInParam("@uid", (DbType)SqlDbType.Int,4,rinfo.uid),
					                DbHelper.MakeInParam("@backup", (DbType)SqlDbType.VarChar,0,rinfo.backup),
                                    DbHelper.MakeInParam("@routeid", (DbType)SqlDbType.Int,4,rinfo.routeid),
                                    DbHelper.MakeInParam("@seacode", (DbType)SqlDbType.Char,40,rinfo.seacode),
                                    
			                        DbHelper.MakeInParam("20GP", (DbType)SqlDbType.VarChar,50, rinfo._20GP),
			                        DbHelper.MakeInParam("40GP", (DbType)SqlDbType.VarChar,50, rinfo._40GP),
			                        DbHelper.MakeInParam("40HQ", (DbType)SqlDbType.VarChar,50, rinfo._40HQ),
			                        DbHelper.MakeInParam("YAS", (DbType)SqlDbType.VarChar,50, rinfo.YAS),
			                        DbHelper.MakeInParam("CIC", (DbType)SqlDbType.VarChar,50, rinfo.CIC),
			                        DbHelper.MakeInParam("EBS", (DbType)SqlDbType.VarChar,50, rinfo.EBS),
			                        DbHelper.MakeInParam("PSS", (DbType)SqlDbType.VarChar,50, rinfo.PSS),
			                        DbHelper.MakeInParam("OTHER", (DbType)SqlDbType.VarChar,50, rinfo.OTHER),
			                        DbHelper.MakeInParam("CAF", (DbType)SqlDbType.VarChar,50, rinfo.CAF),
			                        DbHelper.MakeInParam("BAF", (DbType)SqlDbType.VarChar,50, rinfo.BAF),
			                        DbHelper.MakeInParam("AMS_ENS", (DbType)SqlDbType.VarChar,50, rinfo.AMS_ENS),
			                        DbHelper.MakeInParam("QFF", (DbType)SqlDbType.VarChar,50, rinfo.QFF),
			                        DbHelper.MakeInParam("DCF", (DbType)SqlDbType.VarChar,50, rinfo.DCF),
			                        DbHelper.MakeInParam("XSF", (DbType)SqlDbType.VarChar,50, rinfo.XSF),
			                        DbHelper.MakeInParam("THC", (DbType)SqlDbType.VarChar,50, rinfo.THC),
			                        DbHelper.MakeInParam("ECR", (DbType)SqlDbType.VarChar,50, rinfo.ECR),
			                        DbHelper.MakeInParam("RMB_EBS", (DbType)SqlDbType.VarChar,50, rinfo.RMB_EBS),
			                        DbHelper.MakeInParam("DOC", (DbType)SqlDbType.VarChar,50, rinfo.DOC),
			                        DbHelper.MakeInParam("RMB_CIC", (DbType)SqlDbType.VarChar,50, rinfo.RMB_CIC),
			                        DbHelper.MakeInParam("Extend1", (DbType)SqlDbType.VarChar,50, rinfo.Extend1),
			                        DbHelper.MakeInParam("Extend2", (DbType)SqlDbType.VarChar,50, rinfo.Extend2),
			                        DbHelper.MakeInParam("isconfim", (DbType)SqlDbType.Bit,1, rinfo.isconfim)
                                  };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}routebase_Build", BaseConfigs.GetTablePrefix), parms), -1);
        }
        /// <summary>
        /// 航线基本信息分页操作
        /// </summary>
        public IDataReader GetRouteBaseListByPage(int pageindex, int pagesize, string condition, string ordercolumn, string ordertype)
        {
            DbParameter[] parms = {
                          DbHelper.MakeInParam("@pageindex",(DbType)SqlDbType.Int,4,pageindex),
                          DbHelper.MakeInParam("@pagesize",(DbType)SqlDbType.Int,4,pagesize),
                          DbHelper.MakeInParam("@conditions",(DbType)SqlDbType.NVarChar,2000,condition),
                          DbHelper.MakeInParam("@ordercolumn",(DbType)SqlDbType.VarChar,20,ordercolumn),
                          DbHelper.MakeInParam("@ordertype",(DbType)SqlDbType.VarChar,5,ordertype)
            };
            return DbHelper.ExecuteReader(CommandType.StoredProcedure, string.Format("{0}getroutebasesbypage", BaseConfigs.GetTablePrefix), parms);
        }

        /// <summary>
        /// 航线基本信息分页操作
        /// </summary>
        public IDataReader GetRouteBaseSearchByPage(int type, int oid, int cid, int pageindex, int pagesize, string condition, string ordercolumn, string ordertype)
        {
            DbParameter[] parms = {
                          DbHelper.MakeInParam("@type",(DbType)SqlDbType.Int,4,type),
                          DbHelper.MakeInParam("@oid",(DbType)SqlDbType.Int,4,oid),
                          DbHelper.MakeInParam("@cid",(DbType)SqlDbType.Int,4,cid),
                          DbHelper.MakeInParam("@pageindex",(DbType)SqlDbType.Int,4,pageindex),
                          DbHelper.MakeInParam("@pagesize",(DbType)SqlDbType.Int,4,pagesize),
                          DbHelper.MakeInParam("@conditions",(DbType)SqlDbType.NVarChar,2000,condition),
                          DbHelper.MakeInParam("@ordercolumn",(DbType)SqlDbType.VarChar,20,ordercolumn),
                          DbHelper.MakeInParam("@ordertype",(DbType)SqlDbType.VarChar,5,ordertype)
            };
            return DbHelper.ExecuteReader(CommandType.StoredProcedure, string.Format("{0}getroutebasesearchbypage", BaseConfigs.GetTablePrefix), parms);
        }

        /// <summary>
        /// 航线基本信息分页操作(table)
        /// </summary>
        public DataTable GetRouteBaseSearchTableByPage(int type, int oid, int cid, int pageindex, int pagesize, string condition, string ordercolumn, string ordertype)
        {
            DbParameter[] parms = {
                          DbHelper.MakeInParam("@type",(DbType)SqlDbType.Int,4,type),
                          DbHelper.MakeInParam("@oid",(DbType)SqlDbType.Int,4,oid),
                          DbHelper.MakeInParam("@cid",(DbType)SqlDbType.Int,4,cid),
                          DbHelper.MakeInParam("@pageindex",(DbType)SqlDbType.Int,4,pageindex),
                          DbHelper.MakeInParam("@pagesize",(DbType)SqlDbType.Int,4,pagesize),
                          DbHelper.MakeInParam("@conditions",(DbType)SqlDbType.NVarChar,2000,condition),
                          DbHelper.MakeInParam("@ordercolumn",(DbType)SqlDbType.VarChar,20,ordercolumn),
                          DbHelper.MakeInParam("@ordertype",(DbType)SqlDbType.VarChar,5,ordertype)
            };
            return DbHelper.ExecuteDataset(CommandType.StoredProcedure, string.Format("{0}getroutebasesearchbypage", BaseConfigs.GetTablePrefix), parms).Tables[0];
        }

        /// <summary>
        /// 航线基本信息分页操作
        /// </summary>
        public DataTable GetRouteBaseTableByPage(int pageindex, int pagesize, string condition, string ordercolumn, string ordertype)
        {
            DbParameter[] parms = {
                          DbHelper.MakeInParam("@pageindex",(DbType)SqlDbType.Int,4,pageindex),
                          DbHelper.MakeInParam("@pagesize",(DbType)SqlDbType.Int,4,pagesize),
                          DbHelper.MakeInParam("@conditions",(DbType)SqlDbType.NVarChar,2000,condition),
                          DbHelper.MakeInParam("@ordercolumn",(DbType)SqlDbType.VarChar,20,ordercolumn),
                          DbHelper.MakeInParam("@ordertype",(DbType)SqlDbType.VarChar,5,ordertype)
            };
            return DbHelper.ExecuteDataset(CommandType.StoredProcedure, string.Format("{0}getroutebasesbypage", BaseConfigs.GetTablePrefix), parms).Tables[0];
        }
        /// <summary>
        /// 航线数量
        /// </summary>
        public int GetRouteBasesCount(string conditions)
        {
            DbParameter[] parms = { DbHelper.MakeInParam("@conditions", (DbType)SqlDbType.VarChar, 2000, conditions) };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}getroutebasescountbycondition", BaseConfigs.GetTablePrefix), parms));
        }
        /// <summary>
        /// 搜索航线数量
        /// </summary>
        public int GetRouteBaseListCount(int type, int oid, string conditions)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@type",(DbType)SqlDbType.Int,4,type),
                                      DbHelper.MakeInParam("@oid",(DbType)SqlDbType.Int,4,oid),
                                      DbHelper.MakeInParam("@conditions", (DbType)SqlDbType.VarChar, 2000, conditions)
                                  };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}getroutebasessearchcountbycondition", BaseConfigs.GetTablePrefix), parms));
        }
        /// <summary>
        /// 修改航线信息
        /// </summary>
        /// <param name="rbinfo"></param>
        /// <returns></returns>
        public int UpdateRouteBase(RouteBaseInfo rinfo)
        {
            DbParameter[] parms = {
                                    DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int,4,rinfo.id),
					                DbHelper.MakeInParam("@loadport", (DbType)SqlDbType.Char,40,rinfo.loadport),
					                DbHelper.MakeInParam("@destport", (DbType)SqlDbType.Char,40,rinfo.destport),
					                DbHelper.MakeInParam("@tranport", (DbType)SqlDbType.Char,40,rinfo.tranport),
					                DbHelper.MakeInParam("@bcode", (DbType)SqlDbType.Char,40,rinfo.bcode),
					                DbHelper.MakeInParam("@pier", (DbType)SqlDbType.Char,40,rinfo.pier),
					                DbHelper.MakeInParam("@schbegin", (DbType)SqlDbType.VarChar,20,rinfo.schbegin),
					                DbHelper.MakeInParam("@schend", (DbType)SqlDbType.VarChar,20,rinfo.schend),
					                DbHelper.MakeInParam("@range", (DbType)SqlDbType.VarChar,10,rinfo.range),
					                DbHelper.MakeInParam("@validbegin", (DbType)SqlDbType.SmallDateTime,8,rinfo.validbegin),
					                DbHelper.MakeInParam("@validend", (DbType)SqlDbType.SmallDateTime,8,rinfo.validend),
					                DbHelper.MakeInParam("@bio", (DbType)SqlDbType.VarChar,0,rinfo.bio),
					                DbHelper.MakeInParam("@cid", (DbType)SqlDbType.Int,4,rinfo.cid),
					                DbHelper.MakeInParam("@uid", (DbType)SqlDbType.Int,4,rinfo.uid),
					                DbHelper.MakeInParam("@backup", (DbType)SqlDbType.VarChar,0,rinfo.backup),
                                    DbHelper.MakeInParam("@routeid", (DbType)SqlDbType.Int,4,rinfo.routeid),
                                    DbHelper.MakeInParam("@seacode", (DbType)SqlDbType.Char,40,rinfo.seacode),
                                    
			                        DbHelper.MakeInParam("20GP", (DbType)SqlDbType.VarChar,50, rinfo._20GP),
			                        DbHelper.MakeInParam("40GP", (DbType)SqlDbType.VarChar,50, rinfo._40GP),
			                        DbHelper.MakeInParam("40HQ", (DbType)SqlDbType.VarChar,50, rinfo._40HQ),
			                        DbHelper.MakeInParam("YAS", (DbType)SqlDbType.VarChar,50, rinfo.YAS),
			                        DbHelper.MakeInParam("CIC", (DbType)SqlDbType.VarChar,50, rinfo.CIC),
			                        DbHelper.MakeInParam("EBS", (DbType)SqlDbType.VarChar,50, rinfo.EBS),
			                        DbHelper.MakeInParam("PSS", (DbType)SqlDbType.VarChar,50, rinfo.PSS),
			                        DbHelper.MakeInParam("OTHER", (DbType)SqlDbType.VarChar,50, rinfo.OTHER),
			                        DbHelper.MakeInParam("CAF", (DbType)SqlDbType.VarChar,50, rinfo.CAF),
			                        DbHelper.MakeInParam("BAF", (DbType)SqlDbType.VarChar,50, rinfo.BAF),
			                        DbHelper.MakeInParam("AMS_ENS", (DbType)SqlDbType.VarChar,50, rinfo.AMS_ENS),
			                        DbHelper.MakeInParam("QFF", (DbType)SqlDbType.VarChar,50, rinfo.QFF),
			                        DbHelper.MakeInParam("DCF", (DbType)SqlDbType.VarChar,50, rinfo.DCF),
			                        DbHelper.MakeInParam("XSF", (DbType)SqlDbType.VarChar,50, rinfo.XSF),
			                        DbHelper.MakeInParam("THC", (DbType)SqlDbType.VarChar,50, rinfo.THC),
			                        DbHelper.MakeInParam("ECR", (DbType)SqlDbType.VarChar,50, rinfo.ECR),
			                        DbHelper.MakeInParam("RMB_EBS", (DbType)SqlDbType.VarChar,50, rinfo.RMB_EBS),
			                        DbHelper.MakeInParam("DOC", (DbType)SqlDbType.VarChar,50, rinfo.DOC),
			                        DbHelper.MakeInParam("RMB_CIC", (DbType)SqlDbType.VarChar,50, rinfo.RMB_CIC),
			                        DbHelper.MakeInParam("Extend1", (DbType)SqlDbType.VarChar,50, rinfo.Extend1),
			                        DbHelper.MakeInParam("Extend2", (DbType)SqlDbType.VarChar,50, rinfo.Extend2),
			                        DbHelper.MakeInParam("isconfim", (DbType)SqlDbType.Bit,1, rinfo.isconfim)
                                  };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}routebase_Update", BaseConfigs.GetTablePrefix), parms), -1);
        }
        /// <summary>
        /// 快捷价格修改
        /// </summary>
        /// <param name="rid">线路ID</param>
        /// <param name="price1">20GP</param>
        /// <param name="price2">40GP</param>
        /// <param name="price3">40HQ</param>
        public int UpdateRouteBasePrice(int rid, string price1, string price2, string price3)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("rid",(DbType)SqlDbType.Int,4,rid),
                                      DbHelper.MakeInParam("20GP", (DbType)SqlDbType.VarChar,50, price1),
			                          DbHelper.MakeInParam("40GP", (DbType)SqlDbType.VarChar,50, price2),
			                          DbHelper.MakeInParam("40HQ", (DbType)SqlDbType.VarChar,50, price3)
			                      };
            string commandText = string.Format("update [{0}routeprice] set [20GP]=@20GP,[40GP]=@40GP,[40HQ]=@40HQ WHERE id=@rid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 获取航线实体信息
        /// </summary>
        public IDataReader GetRouteBaseInfo(int id)
        {
            DbParameter[] parms = {
									   DbHelper.MakeInParam("@id",(DbType)SqlDbType.Int,4,id),
			                      };
            return DbHelper.ExecuteReader(CommandType.StoredProcedure, string.Format("{0}getroutebaseinfobyid", BaseConfigs.GetTablePrefix), parms);
        }

        /// <summary>
        /// 获取搜索航线实体信息
        /// </summary>
        public IDataReader GetSearchRouteBaseInfo(int id, int type, int oid,int cid)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@type",(DbType)SqlDbType.Int,4,type),
                                      DbHelper.MakeInParam("@oid",(DbType)SqlDbType.Int,4,oid),
									  DbHelper.MakeInParam("@id",(DbType)SqlDbType.Int,4,id),
                                      DbHelper.MakeInParam("@cid",(DbType)SqlDbType.Int,4,cid)
			                      };
            return DbHelper.ExecuteReader(CommandType.StoredProcedure, string.Format("{0}getroutebasesearchinfobyid", BaseConfigs.GetTablePrefix), parms);
        }

        public int DelRouteBase(string idlist)
        {
            string commandText = string.Format("DELETE [{0}routeprice] WHERE [id] IN (" + idlist + ") ", BaseConfigs.GetTablePrefix, idlist);
            DbHelper.ExecuteNonQuery(CommandType.Text, commandText);
            string commandText2 = string.Format("DELETE [{0}routebase] WHERE [id] IN (" + idlist + ") ", BaseConfigs.GetTablePrefix, idlist);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText2);
        }

        public int UpdateRouteBaseStatus(string ids, int val)
        {
            ids= LTJ.Common.Utils.returnIdList(ids);
            string commandText = string.Format("update [{0}routebase] set isuse=" + val + " WHERE [id] IN (" + ids + ") ", BaseConfigs.GetTablePrefix, ids);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText);
        }

        /// <summary>
        /// 搜索条件生成
        /// </summary>
        /// <param name="cid">所属公司ID</param>
        /// <param name="isuse">是否启用（-1为不限，0为未启用，1，为启用中）</param>
        /// <param name="loadport">起运港口</param>
        /// <param name="objectport">目的港口</param>
        /// <param name="routeline">航线</param>
        /// <param name="corp">船公司</param>
        public string GetRouteBaseCondation(int cid, int isuse, string loadport, string objectport, string routeline, string corp)
        {
            StringBuilder sqlsb = new StringBuilder();
            sqlsb.Append("1=1");
            loadport = Utils.ChkSQL(Utils.RemoveHtml(loadport.Trim()));
            objectport = Utils.ChkSQL(Utils.RemoveHtml(objectport.Trim()));
            routeline = Utils.ChkSQL(Utils.RemoveHtml(routeline.Trim()));
            corp = Utils.ChkSQL(Utils.RemoveHtml(corp.Trim()));

            if (cid > 0) sqlsb.AppendFormat(" AND [cid] = {0}", cid);
            if (isuse > -1 && isuse < 2) sqlsb.AppendFormat(" AND [isuse] = {0}", isuse);
            else if (isuse == 2) sqlsb.Append(" AND [isuse] = 1 AND [routeid] > 0 ");
            if (isuse == -2) sqlsb.Append(" AND [routeid] > 0 ");
            if (!string.IsNullOrEmpty(routeline) && Utils.IsSafeSqlString(routeline)) sqlsb.AppendFormat(" AND (routetcode = '{0}' OR View_routebaseinfo_price.seacode = '{0}')", routeline);
            if (!string.IsNullOrEmpty(loadport) && Utils.IsSafeSqlString(loadport)) sqlsb.AppendFormat(" AND (loadname LIKE '%{0}%' OR loadename LIKE '%{0}%')", loadport);
            if (!string.IsNullOrEmpty(objectport) && Utils.IsSafeSqlString(objectport)) sqlsb.AppendFormat(" AND (destname LIKE '%{0}%' OR destename LIKE '%{0}%')", objectport);
            if (!string.IsNullOrEmpty(corp) && Utils.IsSafeSqlString(corp)) sqlsb.AppendFormat(" AND (routebcode = '{0}' OR bcode = '{0}')", corp);

            return sqlsb.ToString();
        }

        /// <summary>
        /// 批量修改线路价格信息
        /// </summary>
        public int UpdateBatchRoutePrice(RouteBaseInfo rinfo, string ids)
        {
            DbParameter[] parms = {
			                        DbHelper.MakeInParam("20GP", (DbType)SqlDbType.VarChar,50, rinfo._20GP),
			                        DbHelper.MakeInParam("40GP", (DbType)SqlDbType.VarChar,50, rinfo._40GP),
			                        DbHelper.MakeInParam("40HQ", (DbType)SqlDbType.VarChar,50, rinfo._40HQ),
			                        DbHelper.MakeInParam("YAS", (DbType)SqlDbType.VarChar,50, rinfo.YAS),
			                        DbHelper.MakeInParam("CIC", (DbType)SqlDbType.VarChar,50, rinfo.CIC),
			                        DbHelper.MakeInParam("EBS", (DbType)SqlDbType.VarChar,50, rinfo.EBS),
			                        DbHelper.MakeInParam("PSS", (DbType)SqlDbType.VarChar,50, rinfo.PSS),
			                        DbHelper.MakeInParam("OTHER", (DbType)SqlDbType.VarChar,50, rinfo.OTHER),
			                        DbHelper.MakeInParam("CAF", (DbType)SqlDbType.VarChar,50, rinfo.CAF),
			                        DbHelper.MakeInParam("BAF", (DbType)SqlDbType.VarChar,50, rinfo.BAF),
			                        DbHelper.MakeInParam("AMS_ENS", (DbType)SqlDbType.VarChar,50, rinfo.AMS_ENS),
			                        DbHelper.MakeInParam("QFF", (DbType)SqlDbType.VarChar,50, rinfo.QFF),
			                        DbHelper.MakeInParam("DCF", (DbType)SqlDbType.VarChar,50, rinfo.DCF),
			                        DbHelper.MakeInParam("XSF", (DbType)SqlDbType.VarChar,50, rinfo.XSF),
			                        DbHelper.MakeInParam("THC", (DbType)SqlDbType.VarChar,50, rinfo.THC),
			                        DbHelper.MakeInParam("ECR", (DbType)SqlDbType.VarChar,50, rinfo.ECR),
			                        DbHelper.MakeInParam("RMB_EBS", (DbType)SqlDbType.VarChar,50, rinfo.RMB_EBS),
			                        DbHelper.MakeInParam("DOC", (DbType)SqlDbType.VarChar,50, rinfo.DOC),
			                        DbHelper.MakeInParam("RMB_CIC", (DbType)SqlDbType.VarChar,50, rinfo.RMB_CIC),
			                        DbHelper.MakeInParam("Extend1", (DbType)SqlDbType.VarChar,50, rinfo.Extend1),
			                        DbHelper.MakeInParam("Extend2", (DbType)SqlDbType.VarChar,50, rinfo.Extend2),
			                        DbHelper.MakeInParam("isconfim", (DbType)SqlDbType.Bit,1, rinfo.isconfim),
                                    DbHelper.MakeInParam("ids", (DbType)SqlDbType.VarChar,2000, ids)
                                  };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}routepricebatch_Update", BaseConfigs.GetTablePrefix), parms), -1);
        }
        /// <summary>
        /// 批量修改线路基本信息
        /// </summary>
        public int UpdateBatchRouteInfo(RouteBaseInfo rinfo, string ids)
        {
            DbParameter[] parms = {
                                    DbHelper.MakeInParam("@loadport", (DbType)SqlDbType.Char,40,rinfo.loadport),
					                DbHelper.MakeInParam("@destport", (DbType)SqlDbType.Char,40,rinfo.destport),
					                DbHelper.MakeInParam("@tranport", (DbType)SqlDbType.Char,40,rinfo.tranport),
					                DbHelper.MakeInParam("@pier", (DbType)SqlDbType.Char,40,rinfo.pier),
					                DbHelper.MakeInParam("@schbegin", (DbType)SqlDbType.VarChar,20,rinfo.schbegin),
					                DbHelper.MakeInParam("@schend", (DbType)SqlDbType.VarChar,20,rinfo.schend),
					                DbHelper.MakeInParam("@range", (DbType)SqlDbType.VarChar,10,rinfo.range),
					                DbHelper.MakeInParam("@validbegin", (DbType)SqlDbType.SmallDateTime,8,rinfo.validbegin),
					                DbHelper.MakeInParam("@validend", (DbType)SqlDbType.SmallDateTime,8,rinfo.validend),
					                DbHelper.MakeInParam("@bio", (DbType)SqlDbType.VarChar,0,rinfo.bio),
                                    DbHelper.MakeInParam("@routeid", (DbType)SqlDbType.Int,4,rinfo.routeid),
                                    DbHelper.MakeInParam("ids", (DbType)SqlDbType.VarChar,2000, ids)
                                  };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}routeinfobatch_Update", BaseConfigs.GetTablePrefix), parms), -1);
        }
        #endregion

        #region 规则信息操作
        /// <summary>
        /// 增设规则信息
        /// </summary>
        public int CreateRules(int uid, int oid, int rtype, string _20GP, string _40GP, string _40HQ, int rid, string port, string seacode)
        {
            DbParameter[] parms = {
			                        DbHelper.MakeInParam("uid", (DbType)SqlDbType.Int,4, uid),
			                        DbHelper.MakeInParam("oid", (DbType)SqlDbType.Int,4, oid),
			                        DbHelper.MakeInParam("rtype", (DbType)SqlDbType.Int,4, rtype),
			                        DbHelper.MakeInParam("20GP", (DbType)SqlDbType.VarChar,50, _20GP),
			                        DbHelper.MakeInParam("40GP", (DbType)SqlDbType.VarChar,50, _40GP),
			                        DbHelper.MakeInParam("40HQ", (DbType)SqlDbType.VarChar,50, _40HQ),
			                        DbHelper.MakeInParam("rid", (DbType)SqlDbType.Int,4, rid),
			                        DbHelper.MakeInParam("port", (DbType)SqlDbType.Char,40, port),
                                    DbHelper.MakeInParam("seacode", (DbType)SqlDbType.Char,40, seacode),
                                  };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}rule_ADD", BaseConfigs.GetTablePrefix), parms), -1);
        }
        /// <summary>
        /// 是否存在规则信息
        /// </summary>
        /// <param name="uid">用户ID</param>
        /// <param name="oid">企业ID</param>
        /// <param name="rtype">规则类型</param>
        /// <param name="rid">航线ID</param>
        /// <param name="port">港口</param>
        public bool ExistRule(int uid, int oid, int rtype, int rid, string port, string seacode)
        {
            DbParameter[] parms = {
			                        DbHelper.MakeInParam("uid", (DbType)SqlDbType.Int,4, uid),
			                        DbHelper.MakeInParam("oid", (DbType)SqlDbType.Int,4, oid),
			                        DbHelper.MakeInParam("rtype", (DbType)SqlDbType.Int,4, rtype),
			                        DbHelper.MakeInParam("rid", (DbType)SqlDbType.Int,4, rid),
			                        DbHelper.MakeInParam("port", (DbType)SqlDbType.Char,40, port),
                                    DbHelper.MakeInParam("seacode",(DbType)SqlDbType.Char,40,seacode)
                                  };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}rule_Exist", BaseConfigs.GetTablePrefix), parms), 0) > 0;
        }
        /// <summary>
        /// 获取规则数量
        /// </summary>
        public int GetRulesCount(string conditions)
        {
            DbParameter[] parms = { DbHelper.MakeInParam("@conditions", (DbType)SqlDbType.VarChar, 2000, conditions) };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}getrulecountbycondition", BaseConfigs.GetTablePrefix), parms));
        }

        /// <summary>
        /// 规则基本信息分页操作（DataTable对象)
        /// </summary>
        public DataTable GetRulesByPage(int pageindex, int pagesize, string condition, string ordercolumn, string ordertype)
        {
            DbParameter[] parms = {
                          DbHelper.MakeInParam("@pageindex",(DbType)SqlDbType.Int,4,pageindex),
                          DbHelper.MakeInParam("@pagesize",(DbType)SqlDbType.Int,4,pagesize),
                          DbHelper.MakeInParam("@conditions",(DbType)SqlDbType.NVarChar,2000,condition),
                          DbHelper.MakeInParam("@ordercolumn",(DbType)SqlDbType.VarChar,20,ordercolumn),
                          DbHelper.MakeInParam("@ordertype",(DbType)SqlDbType.VarChar,5,ordertype)
            };
            return DbHelper.ExecuteDataset(CommandType.StoredProcedure, string.Format("{0}getrulesbypage", BaseConfigs.GetTablePrefix), parms).Tables[0];
        }

        /// <summary>
        /// 批量删除操作
        /// </summary>
        public void DeleteRulesByIds(string ids)
        {
            string commandText = string.Format("DELETE [{0}routerule] WHERE [id] IN ({1})", BaseConfigs.GetTablePrefix, ids);
            DbHelper.ExecuteNonQuery(CommandType.Text, commandText);
        }

        /// <summary>
        /// 获取规则实体
        /// </summary>
        public IDataReader GetRuleInfoByID(int id)
        {
            DbParameter[] parms = { DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, 4, id) };
            return DbHelper.ExecuteReader(CommandType.StoredProcedure, string.Format("{0}getrulemodelbyid", BaseConfigs.GetTablePrefix), parms);
        }

        /// <summary>
        /// 修改规则价格设置
        /// </summary>
        public void UpdateRulePrice(int id, string price1, string price2, string price3)
        {
            DbParameter[] parms = { DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, 4, id) };
            string commandText = string.Format("UPDATE [{0}routerule] SET [20GP] = '{1}',[40GP] = '{2}',[40HQ] = '{3}' WHERE [id]=@id", BaseConfigs.GetTablePrefix, price1, price2, price3);
            DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 其他公司航线价格分页操作
        /// </summary>
        public DataTable GetRoutePriceRuleByPage(int type, int uid, int cid, int pageindex, int pagesize, string condition, string ordercolumn, string ordertype)
        {
            DbParameter[] parms = {
                          DbHelper.MakeInParam("@uid",(DbType)SqlDbType.Int,4,uid),
                          DbHelper.MakeInParam("@cid",(DbType)SqlDbType.Int,4,cid),
                          DbHelper.MakeInParam("@pageindex",(DbType)SqlDbType.Int,4,pageindex),
                          DbHelper.MakeInParam("@pagesize",(DbType)SqlDbType.Int,4,pagesize),
                          DbHelper.MakeInParam("@conditions",(DbType)SqlDbType.NVarChar,2000,condition),
                          DbHelper.MakeInParam("@ordercolumn",(DbType)SqlDbType.VarChar,20,ordercolumn),
                          DbHelper.MakeInParam("@ordertype",(DbType)SqlDbType.VarChar,5,ordertype)
            };
            return DbHelper.ExecuteDataset(CommandType.StoredProcedure, string.Format("{0}getroutepricerulebypage", BaseConfigs.GetTablePrefix), parms).Tables[0];
        }
        /// <summary>
        /// 其他公司线路价格规则批量调整
        /// </summary>
        /// <param name="ids">线路ID集合</param>
        public void UpdateRules(string ids, int uid, string price1, string price2, string price3)
        {
            DbParameter[] parms = {
                                    DbHelper.MakeInParam("ids", (DbType)SqlDbType.VarChar,2000, ids),
                                    DbHelper.MakeInParam("uid", (DbType)SqlDbType.Int,4, uid),
                                    DbHelper.MakeInParam("20GP", (DbType)SqlDbType.VarChar,50, price1),
                                    DbHelper.MakeInParam("40GP", (DbType)SqlDbType.VarChar,50, price2),
                                    DbHelper.MakeInParam("40HQ", (DbType)SqlDbType.VarChar,50, price3)
                                  };
            DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}routepricerule_update", BaseConfigs.GetTablePrefix), parms);
        }
        /// <summary>
        /// 其他公司线路价格规则批量调整（用户批量）
        /// </summary>
        public void UpdateRules(string ids, string uids, string price1, string price2, string price3)
        {
            DbParameter[] parms = {
                                    DbHelper.MakeInParam("ids", (DbType)SqlDbType.VarChar,2000, ids),
                                    DbHelper.MakeInParam("uids", (DbType)SqlDbType.VarChar,500, uids),
                                    DbHelper.MakeInParam("20GP", (DbType)SqlDbType.VarChar,50, price1),
                                    DbHelper.MakeInParam("40GP", (DbType)SqlDbType.VarChar,50, price2),
                                    DbHelper.MakeInParam("40HQ", (DbType)SqlDbType.VarChar,50, price3)
                                  };
            DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}routepricerules_update", BaseConfigs.GetTablePrefix), parms);
        }
        #endregion
    }
}
